package puppy.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;



import util.webSearch.WebResult;

/**
 * 
 * @author Sergio Duarte
 * 
 */
public class DBHandler implements DBParameters {

	/**
	 * @param args
	 */

	static private Connection con = null;

	//private static org.apache.log4j.Logger log = Logger.getLogger(DBHandler.class);
	
	/**
	 * Returns query text [0] and query description [1] for a query 
	 * specified with its id
	 *  
	 * 
	 * @param queryId
	 * @return
	 */
	public static String[] getQueryInfo(Long queryId) {

		String[] q = new String[2];

		try {
			Class.forName("com.mysql.jdbc.Driver");

			con = DriverManager.getConnection(
					"jdbc:mysql://localhost/puppyForm", "root", "thesis");
			Statement st = con.createStatement();

			ResultSet rs = st
					.executeQuery("select query,description from QuerySet where"
							+ " id=" + "" + queryId + "");

			while (rs.next()) {

				String query = rs.getString("query");

				String desc = rs.getString("description");

				q[0] = query;
				q[1] = desc;
			}

			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return q;
	}
	
	
	public static LinkedList<String> getUrlsForQuery(Long queryId) {

		LinkedList<String> list = new LinkedList<String>();

		try {
			Class.forName("com.mysql.jdbc.Driver");

			con = DriverManager.getConnection(
					"jdbc:mysql://localhost/puppyForm", "root", "thesis");
			Statement st = con.createStatement();

			ResultSet rs = st
					.executeQuery("select distinct url from QueryData where"
							+ " queryId=" + "'" + queryId + "'");

			while (rs.next()) {

				String url = rs.getString("url");

				list.add(url);

			}

			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return list;
	}

	 synchronized public static LinkedList<Long> getQueriesID() {

		LinkedList<Long> set = new LinkedList<Long>();

		try {
			
			
			
		/*	Class.forName("com.mysql.jdbc.Driver").newInstance();
			
			Context initCtx = new InitialContext();
			Context envCtx = (Context) initCtx.lookup("java:comp/env");

			// Look up our data source
			DataSource ds = (DataSource)
			  envCtx.lookup("jdbc/puppyWebForm");*/

			// Allocate and use a connection from the pool
						
			Class.forName("com.mysql.jdbc.Driver");
			String connectionURL="jdbc:mysql://127.0.0.1:3306/puppyForm";
			Connection connection = DriverManager.getConnection(connectionURL,"root","thesis");
			
			Statement st = connection.createStatement();

			ResultSet rs = st.executeQuery("select id from QuerySet where "
					+ " reviews=0");

			while (rs.next()) {

				Long id = rs.getLong("id");
		//		log.error("Insertando " + id);
				set.add(id);

			}
			/*st.close();
			rs.close();*/
			connection.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		//	log.fatal("se totio 1", e);
			
		//	log.fatal("mensaje "+ e.getMessage());
		//	log.fatal(e.getErrorCode());
		
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
		
		//	log.fatal("se totio 2");
			e.printStackTrace();
		} 
		return set;
	}
	 	 
	 
	 
	 synchronized public static Long getQueryID(String query) {

			Long id= new Long(0);

			try {
				
				
				
			/*	Class.forName("com.mysql.jdbc.Driver").newInstance();
				
				Context initCtx = new InitialContext();
				Context envCtx = (Context) initCtx.lookup("java:comp/env");

				// Look up our data source
				DataSource ds = (DataSource)
				  envCtx.lookup("jdbc/puppyWebForm");*/

				// Allocate and use a connection from the pool
							
				Class.forName("com.mysql.jdbc.Driver");
				String connectionURL="jdbc:mysql://127.0.0.1:3306/puppyForm";
				Connection connection = DriverManager.getConnection(connectionURL,"root","thesis");
				
				Statement st = connection.createStatement();

				ResultSet rs = st.executeQuery("select id from QuerySet where "
						+ " query=\"" + query + "\"");

				while (rs.next()) {

					id = rs.getLong("id");
			//		log.error("Insertando " + id);
					

				}
				/*st.close();
				rs.close();*/
				connection.close();

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			//	log.fatal("se totio 1", e);
				
			//	log.fatal("mensaje "+ e.getMessage());
			//	log.fatal(e.getErrorCode());
			
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
			
			//	log.fatal("se totio 2");
				e.printStackTrace();
			} 
			return id;
		}
		 	 
	 
	 
	 /**
	  * Get set of web entry ids that share the same url 
	  * 
	  * @param url
	  * @return
	  */
	public static HashSet<Long> getEntriesWithSameURL(String url) {

		HashSet<Long> ids = new HashSet<Long>();

		try {
			Class.forName("com.mysql.jdbc.Driver");

			con = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/puppyForm", "root", "thesis");
			Statement st = con.createStatement();

			ResultSet rs = st.executeQuery("select id from QueryData where "
					+ " url='" + url + "'");

			while (rs.next()) {

				Long id = rs.getLong("id");

				ids.add(id);

			}
			st.close();
			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ids;
	}
	
	
	
	public static void updateQueryRevisions(){
		
		
	}
	
	public static void insertWebResults(LinkedList<WebResult> results) {

		try {
			Class.forName("com.mysql.jdbc.Driver");

			con = DriverManager.getConnection(
					"jdbc:mysql://localhost/puppyForm", "root", "thesis");

			for (int i = 0; i < results.size(); i++) {
				WebResult result = results.get(i);

				String INSERT_RECORD = "INSERT INTO QueryData("
						+ "url, searchEngine, rank, queryID, snippet, "
						+ "title, query)" + " values (?,?,?,?,?,?,?)";

				PreparedStatement pstmt = con.prepareStatement(INSERT_RECORD);

				pstmt.setString(1, result.getUrl().trim()); // relevant
				pstmt.setInt(2, result.getEngine()); // appropriate
				pstmt.setInt(3, result.getRank()); // time stamp
				pstmt.setLong(4, result.getQueryID()); // duration
				pstmt.setString(5, result.getSnippet()); // user id
				pstmt.setString(6, result.getTitle()); // male =true
				pstmt.setString(7, result.getQuery()); // male =true
				pstmt.executeUpdate();
			}

			con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	
	public static void main(String[] args) {
		// TODO Auto-generated method stub
/*		DBHandler.getUrlForQuery("1");
		UserFormData data = new UserFormData();
		data.setAge12(true);
		data.setAppropriate(true);
		data.setRelevant(false);
		data.setDuration((long) 200);
		data.setQueryID((long) 10);

		data.setGender((short) 1);
		java.sql.Timestamp sqlDate = new java.sql.Timestamp(
				new java.util.Date().getTime());
		data.setSqlDate(sqlDate);
		data.setUserID((long) 200);

		HashSet<Long> set = new HashSet<Long>();
		set.add(new Long(100));
		DBHandler.insertUserFormInstance(data, set);*/
		LinkedList<Long> list = new LinkedList<Long>();
		list = DBHandler.getQueriesID();
		 float x = (float)(Math.random());
		//System.out.println(DBHandler.getQueryInfo(new Long(1)));
		System.out.println(list.size());
		
		

		
		//System.out.println(currentTimeInSeconds);

	}

}

